Introduction

The following datasets are used in this demonstration:

• 2011 Census data - population and household estimates, available here.
• ONS - House price to residence-based earnings ratio, available here
• Kaggle - COVID19 related tweets, available here

Table of contents

1. Population and household estimates (univariate)

   1.1 Gender ratio

   1.2 Gender by region

   1.3 Population by outward postcode using choropleth map

   1.4 Population by postcode parent area using choropleth map

   1.5 Male/Female ratio by postcode parent area using choropleth map

2. House prices and earnings (multivariate)

   2.1 Correlation between median house price and median household earning

   2.2 Median and lower quartile house prices from 2002 to 2020 (animated)

3. Text analysis and visualisation

   3.1 Text tokenization and word cloud

   3.2 Basic sentiment analysis with VADER

---

Preparation

Please run this code cell to install required packages. If this does not work, please install these packages manually in your virtual environment.

# Install packages using jupyter notebook's built-in %pip function
%pip install pandas plotly geojson shapely openpyxl scipy nltk

Requirement already satisfied: pandas in d:\repository\ox-interview\venv\lib\site-packages (1.2.4)
Requirement already satisfied: plotly in d:\repository\ox-interview\venv\lib\site-packages (4.14.3)
Requirement already satisfied: geojson in d:\repository\ox-interview\venv\lib\site-packages (2.5.0)
Requirement already satisfied: shapely in d:\repository\ox-interview\venv\lib\site-packages (1.7.1)
Requirement already satisfied: openpyxl in d:\repository\ox-interview\venv\lib\site-packages (3.0.7)
Requirement already satisfied: scipy in d:\repository\ox-interview\venv\lib\site-packages (1.6.3)
Requirement already satisfied: nltk in d:\repository\ox-interview\venv\lib\site-packages (3.6.2)
Requirement already satisfied: pytz>=2017.3 in d:\repository\ox-interview\venv\lib\site-packages (from pandas) (2021.1)
Requirement already satisfied: numpy>=1.16.5 in d:\repository\ox-interview\venv\lib\site-packages (from pandas) (1.20.3)
Requirement already satisfied: python-dateutil>=2.7.3 in d:\repository\ox-interview\venv\lib\site-packages (from pandas) (2.8.1)
Requirement already satisfied: six>=1.5 in d:\repository\ox-interview\venv\lib\site-packages (from python-dateutil>=2.7.3->pandas) (1.16.0)
Requirement already satisfied: retrying>=1.3.3 in d:\repository\ox-interview\venv\lib\site-packages (from plotly) (1.3.3)
Requirement already satisfied: et-xmlfile in d:\repository\ox-interview\venv\lib\site-packages (from openpyxl) (1.1.0)
Requirement already satisfied: tqdm in d:\repository\ox-interview\venv\lib\site-packages (from nltk) (4.60.0)
Requirement already satisfied: regex in d:\repository\ox-interview\venv\lib\site-packages (from nltk) (2021.4.4)
Requirement already satisfied: click in d:\repository\ox-interview\venv\lib\site-packages (from nltk) (8.0.0)
Requirement already satisfied: joblib in d:\repository\ox-interview\venv\lib\site-packages (from nltk) (1.0.1)
Requirement already satisfied: colorama in d:\repository\ox-interview\venv\lib\site-packages (from click->nltk) (0.4.4)
Note: you may need to restart the kernel to use updated packages.

Import packages to be used and replace default plotting backend.

# Import required packages
import pandas as pd
import numpy as np
import json
from pathlib import Path
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import re
import shapely.geometry
from shapely.ops import unary_union
import geojson
import itertools
import wordcloud
import nltk
from nltk.sentiment.vader import SentimentIntensityAnalyzer

# Set default pandas plotting backend to Plotly
pd.options.plotting.backend = "plotly"

1. Population and household estimates

# Load data
df1 = pd.read_csv("../data/ons/population_household.csv")

# Split postcode into outward and inward, e.g. LE3 9QP => LE3 and 9QP
df1["postcode_out"] = df1["Postcode"].apply(lambda x: x[0:4].strip())
df1["postcode_in"] = df1["Postcode"].apply(lambda x: x[4:].strip())

# Keep only non-numeric part in outward as the parent region
df1["postcode_region"] = df1["postcode_out"].apply(lambda x: re.split("\d+", x)[0])

# Remove column [Postcode] to save memory
df1 = df1.drop(["Postcode"], axis=1)

1.1 Gender ratio

# Count total number of males and females
df_gender = pd.DataFrame({"Gender": ["Male", "Female"], "Count": [df1["Males"].sum(), df1["Females"].sum()]})

# Generate pie chart
gender_pie = px.pie(df_gender, values="Count", names="Gender", title="National gender ratio (England and Wales)")
gender_pie.show()

# Remove variables to save memory
del df_gender, gender_pie

1.2 Population and gender ratio by region

# Sum up all numbers within same postcode region, descending order by column [Total]
df_temp = df1.groupby(["postcode_region"]).sum().sort_values(by=['Total'], ascending=False).reset_index()

# Generate plots side by side
fig_temp = make_subplots(rows=1, cols=2, specs=[[{}, {}]], shared_xaxes=True,
                    shared_yaxes=False, vertical_spacing=0.001)

# Add plot 1: bar chart for top 10 population regions
fig_temp.append_trace(go.Bar(
    x=df_temp.head(10)["Total"],
    y=df_temp.head(10)["postcode_region"],
    marker=dict(
        color='rgba(50, 171, 96, 0.6)',
        line=dict(
            color='rgba(50, 171, 96, 1.0)',
            width=1),
    ),
    name='Population',
    orientation='h',
), 1, 1)

# Add plot 2: scatter chart for gender ratio in top 10 population regions
fig_temp.append_trace(go.Scatter(
    x=df_temp.head(10)["Males"]/df_temp.head(10)["Females"], y=df_temp.head(10)["postcode_region"],
    mode='lines+markers',
    line_color='rgb(128, 0, 128)',
    name='M/F ratio',
), 1, 2)

# Update layout changes on plot title, axes, color and legends
fig_temp.update_layout(
    title='Male/Female ratio in top 10 population regions',
    yaxis=dict(
        showgrid=False,
        showline=False,
        showticklabels=True,
        domain=[0, 0.85],
    ),
    yaxis2=dict(
        showgrid=False,
        showline=True,
        showticklabels=False,
        linecolor='rgba(102, 102, 102, 0.8)',
        linewidth=2,
        domain=[0, 0.85],
    ),
    xaxis=dict(
        zeroline=False,
        showline=False,
        showticklabels=True,
        showgrid=True,
        domain=[0, 0.42],
    ),
    xaxis2=dict(
        zeroline=False,
        showline=False,
        showticklabels=True,
        showgrid=True,
        domain=[0.47, 1],
        side='top',
        dtick=25000,
    ),
    legend=dict(x=0.029, y=1.038, font_size=10),
    margin=dict(l=100, r=20, t=70, b=70),
    paper_bgcolor='rgb(248, 248, 255)',
    plot_bgcolor='rgb(248, 248, 255)',
)

# Add annotations to the plot
annotations = []

# Adding labels to charts
for a_mf, a_pop, x_pcr in zip(np.round(df_temp.head(10)["Males"]/df_temp.head(10)["Females"], decimals=2), df_temp.head(10)["Total"], df_temp.head(10)["postcode_region"]):
    # Add label to M/F ratio scatter plot
    annotations.append(dict(xref='x2', yref='y2',
                            y=x_pcr, x=a_mf,
                            text=a_mf,
                            xshift=50,
                            showarrow=False))
    # Add label to population bar chart
    annotations.append(dict(xref='x1', yref='y1',
                            y=x_pcr, x=a_pop,
                            text=f"{np.round(a_pop/1000000, 3)}M",
                            xshift=25,
                            showarrow=False))

fig_temp.update_layout(annotations=annotations)
fig_temp.show()

# Remove variables to save memory
del df_temp, annotations, fig_temp

1.3 Population by outward postcode using choropleth map

Concat individual postcode geojson mapping into single variable geojson_uk.

# Create parent geojson collection object
geojson_uk_granulated = dict(type="FeatureCollection", features=list())
# Load all geojson files
path_geojson = Path("../data/geojson")
for f_geojson in list(path_geojson.glob("*.geojson")):
    with open(f_geojson) as f:
        geojson_data = json.load(f)
        for feature in geojson_data["features"]:
            # Add feature id using properties.name, which is the outward postcode
            feature["id"] = feature["properties"]["name"]
            # Add feature to parent geojson collection
            geojson_uk_granulated["features"].append(feature)

Plot population with postcode area. Please note that as the opensource geographical data I used in this demonstration comes from Wikipedia, it does not cover all England regions. This leads to white areas on the map.

# Sum up all numbers within same postcode outward area
df_temp = df1.groupby(["postcode_out"]).sum().reset_index()

# Get max population number among areas
p_max = df_temp["Total"].max()

# Plot figure
fig = px.choropleth_mapbox(df1.groupby(["postcode_out"]).sum().reset_index(), geojson=geojson_uk_granulated,
                           locations='postcode_out', color='Total',
                           color_continuous_scale="jet",
                           range_color=(0, p_max),
                           mapbox_style="carto-positron",
                           zoom=4.5, center={"lat": 52.5, "lon": -1.6},
                           opacity=0.5
                           )
fig.update_layout(margin={"r": 0, "t": 0, "l": 0, "b": 0})
fig.show()

# Remove variables to save memory
del df_temp, p_max, fig

1.4 Population by postcode parent area using choropleth map

In the above plot, regions are probably too granulated, so you would not be able to see an obvious trend. Let's merge them into parent regions.

# Create parent geojson collection object
geojson_uk_regional = dict(type="FeatureCollection", features=list())

# Load all geojson files
path_geojson = Path("../data/geojson")
for f_geojson in list(path_geojson.glob("*.geojson")):
    with open(f_geojson) as f:
        geojson_data = json.load(f)
        # Merge granulated geometry areas into parent region
        merged = unary_union(
            list(
                map(
                    (lambda x: shapely.geometry.asShape(x["geometry"])),
                    geojson_data["features"])
            )
        )
        # Create new geojson feature from merged geometry
        geojson_merged = geojson.Feature(geometry=merged,
                                         properties={"name": f_geojson.stem},
                                         id=f_geojson.stem)
        # Add feature to parent geojson collection
        geojson_uk_regional["features"].append(geojson_merged)

Now we have merged

# Sum up all numbers within same postcode region
df_temp = df1.groupby(["postcode_region"]).sum().reset_index()

# Get max population number among regions
p_max = df_temp["Total"].max()

# Plot figure
fig = px.choropleth_mapbox(df_temp,
                           geojson=geojson_uk_regional,
                           locations='postcode_region',
                           color='Total',
                           color_continuous_scale="jet",
                           range_color=(0, p_max),
                           mapbox_style="carto-positron",
                           zoom=4.5, center={"lat": 52.5, "lon": -1.6},
                           opacity=0.5
                           )
fig.update_layout(margin={"r": 0, "t": 0, "l": 0, "b": 0})
fig.show()

# Remove variables to save memory
del df_temp, p_max, fig

# Remove dataframe used for section 1
del df1

2. House prices and earnings

Define a reusable function to read from the source file and process into a DataFrame in required format.

def read_df2(source, sheet, skip_top, column_name):
    """
    :param source: str or path object, where the source excel file located
    :param sheet: str, name of the sheet in excel file to be read
    :param skip_top: int, how many rows at the top of the sheet to be skipped
    :param column_name: str, new column name to be given to the variable
    :return: DataFrame
    """

    def transpose_df2(r, c_name):
        """
        :param r: Series, a row from the DataFrame
        :param c_name: str, new name to be given to transposed column
        :return: DataFrame
        """
        # row -> column -> new DataFrame
        new_df = r[year_columns].T.to_frame().reset_index(drop=True)
        # Rename the column
        new_df.columns = [c_name]
        # Add column [Region] and populate the whole column with value from cell [Region] in the row
        new_df["Region"] = r["Region"].strip()
        # Add column [Year] and populate the column from 2002 to 2020
        new_df["Year"] = range(2002, 2021)
        return new_df

    # Read target sheet from source file into DataFrame -> drop column [Code] -> drop row if any cell in column [Name] is empty
    temp = pd.read_excel(source, sheet_name=sheet, skiprows=skip_top).drop(columns=["Code"]).dropna(subset=["Name"])
    # As the source file contains empty columns, drop them
    empty_cols = [col for col in temp.columns if temp[col].isnull().all()]
    temp.drop(empty_cols, axis=1, inplace=True)
    # Columns names are inconsistent in different sheets, replace them with standard year based column names
    year_columns = [str(x) for x in range(2002, 2021)]
    temp.columns = ["Region"] + year_columns
    temp.loc[0][year_columns].T.reset_index(drop=True)
    new_temp = pd.DataFrame(columns=["Region", "Year", column_name])
    # Transpose and concat each row into the final DataFrame
    for _, row in temp.iterrows():
        new_temp = pd.concat([new_temp, transpose_df2(row, column_name)])
    # Convert numeric column data type to integer
    new_temp = new_temp.astype({column_name: 'int32'})
    return new_temp

Read dataset into DataFrame

# Read sheet [1a] for the median house price by country and region, England and Wales, 2002 - 2020
df2_1a = read_df2("../data/ons/house_price_earning.xlsx", "1a", 6, "Median house price")
# Read sheet [1b] for the median gross annual earnings by country and region, England and Wales, 2002 - 2020
df2_1b = read_df2("../data/ons/house_price_earning.xlsx", "1b", 6, "Median earn")
# Read sheet [2a] for the lower quartile house price by country and region, England and Wales, 2002 - 2020
df2_2a = read_df2("../data/ons/house_price_earning.xlsx", "2a", 6, "Lower house price")
# Read sheet [1b] for the lower quartile gross annual earnings by country and region, England and Wales, 2002 - 2020
df2_2b = read_df2("../data/ons/house_price_earning.xlsx", "2b", 6, "Lower earn")
# Merge the four DataFrame above into new DataFrame
df2_final = pd.merge(df2_1a, df2_1b, on=["Region", "Year"])
df2_final = pd.merge(df2_final, df2_2a, on=["Region", "Year"])
df2_final = pd.merge(df2_final, df2_2b, on=["Region", "Year"])
# Show top 5 rows of processed DataFrame
df2_final.head(5)
# Delete variables to save memory
del df2_1a, df2_1b, df2_2a, df2_2b

2.1 Correlation between median house price and median household earning

• Scatter plot and trendlines show the correlations between median house price and median earn.
• Top box plot shows the distribution of median house price.
• Right rug box shows the distribution of median earn.

df2_fig1 = px.scatter(df2_final[df2_final.Region.isin(["England", "Wales", "London"])], x="Median house price", y="Median earn", color="Region",
                 marginal_x="box", marginal_y="rug", trendline="ols")
df2_fig1.show()

2.2 Median and lower quartile house prices from 2002 to 2020 (animated)

df2_temp = pd.DataFrame(columns=["Region", "Year", "Variable", "Value"])
for _, row in df2_final.iterrows():
    df2_temp = df2_temp.append([{"Region": row["Region"], "Year": row["Year"], "Variable": "Median house price", "Value": row["Median house price"]},
                              {"Region": row["Region"], "Year": row["Year"], "Variable": "Median earn", "Value": row["Median earn"]}, {"Region": row["Region"], "Year": row["Year"], "Variable": "Lower house price", "Value": row["Lower house price"]}, {"Region": row["Region"], "Year": row["Year"], "Variable": "Lower earn", "Value": row["Lower earn"]}], ignore_index=True)

df2_fig2 = px.bar(df2_temp[df2_temp["Variable"].isin(["Median house price", "Lower house price"])], x="Region", y="Value", text="Value", color="Variable", barmode="group", animation_frame="Year", animation_group="Region", range_y=[0,500000])
df2_fig2.update_yaxes(title_text="Price", secondary_y=False)
df2_fig2.update_yaxes(title_text="Earn", secondary_y=True)
df2_fig2.show()

del df2_final, df2_temp, df2_fig1, df2_fig2

3. Text based analysis and visualisation

3.1 Text tokenization and word cloud

df3 = pd.read_csv("../data/tweet/covid19_tweets.csv")[["user_name", "text", "date"]].sort_values(by='date', ascending=False)
df3['date']=pd.to_datetime(df3['date'], format="%Y-%m-%d %H:%M:%S")
df3["day"] = df3["date"].apply(lambda x: x.date())

# Use basic WhitespaceTokenizer, which will segment text into individual words simply using whitespace
tokenizer = nltk.WhitespaceTokenizer()

# Create a new column [tokens] for tokens
df3["tokens"] = df3["text"].apply(lambda x: tokenizer.tokenize(x.lower()))

# Flatten the tokens into one list and remove some not so important words. In real case, this could combine with part-of-speech (POS) tagging. Here we only use a list of adjectives, conjunctions, articles and prepositions.
not_so_important_words = ["the", "a", "an", "from", "to", "at", "of", "on", "in", "and", "or", "is", "are", "was", "am", "were", "been", "have", "has", "had", "for", "with", "this", "that", "&", "be", "as", "-"]
all_tokens = [x for x in list(itertools.chain.from_iterable(df3["tokens"].tolist())) if not x.startswith("@") and not x.startswith("http") and x not in not_so_important_words and len(x) >= 3]

# Calculate frequencies of each token
token_frequencies = nltk.FreqDist(all_tokens)

# Create a word cloud
wcloud = wordcloud.WordCloud(background_color='white',width=1200, height=600).generate_from_frequencies(token_frequencies)
fig_wc = px.imshow(wcloud)
fig_wc.update_layout(xaxis={"visible": False}, yaxis={"visible": False})
fig_wc.show()

del tokenizer, not_so_important_words, all_tokens, token_frequencies, wcloud, fig_wc

3.2 Basic sentiment analysis with VADER

We can use nltk's built-in VADER model to perform a basic sentiment analysis on these tweets without the need for training.

# Download lexicon resource required for using the VADER model
nltk.download('vader_lexicon')
# Create an analyzer instance
sia = SentimentIntensityAnalyzer()
# Create a function that returns a tuple of sentiment analysis result from the analyzer
def sentiment_analysis(text):
    result = sia.polarity_scores(text)
    return result["neg"], result["neu"], result["pos"], result["compound"], "Positive" if result["compound"] > 0.05 else "Negative" if result["compound"] < -0.05 else "Neutral"
# Create new columns with sentiment analysis result
df3["Negative"], df3["Neutral"], df3["Positive"], df3["Compound"],df3["Overall"] = zip(*df3["text"].map(sentiment_analysis))

[nltk_data] Downloading package vader_lexicon to
[nltk_data]     C:\Users\colin\AppData\Roaming\nltk_data...
[nltk_data]   Package vader_lexicon is already up-to-date!

# Create pie chart based on overall sentiment analysis result from the compound value
fig_senti = px.pie(df3.groupby("Overall").count().reset_index(), values="user_name", names="Overall", title="Sentimental results")
fig_senti.show()

# Delete variable to save memory
del fig_senti

# Unpivot the dataframe
df3_melted = pd.melt(df3, id_vars=["user_name", "text", "tokens", "date", "Overall", "day"], value_vars=["Negative", "Neutral", "Positive", "Compound"])
fig = px.box(df3_melted[(df3_melted["Overall"] == "Negative") & (df3_melted["variable"] == "Negative")], x="day", y="value", labels={
                     "value": "Negative score"
                 })
fig.show()
del df3_melted, fig

df3_by_day = df3.groupby(["Overall", "day"]).count().reset_index().sort_values(by="day")

fig = px.line(df3_by_day, x="day", y="user_name", color="Overall", title="Positive/Negative/Neutral tweet counts over days", labels={
                     "user_name": "Count"
                 })
fig.update_layout()
fig.show()

del df3_by_day, fig

del df3